/*
 * Copyright (C) Francisco Morero Peyrona. All rights reserved.
 * 
 * This software is published under the terms of Open Source License version 1.1, a copy of which has been included with this
 * distribution in the License.txt file.
 */

package agents;

import java.sql.*;

import jdbcmanager.app.App;
import jdbcmanager.dbtools.*;
import jdbcmanager.dbtools.agentwise.*;
import jdbcmanager.dbtools.datatypes.*;

/**
 * A base SqlAgent class that uses standard ANSI SQL92 statements.
 * <p>
 * Note: It is highly recommended that SQL Agents are created by inheriting (extending) this class.
 * 
 * @author Francisco Morero Peyrona
 */
public class AgentSQLSybase extends AgentSQLDefault
{
	//-------------------------------------------------------------------------//
	// CATALOG RELATED METHODS
	//-------------------------------------------------------------------------//
	/**
	 * See <code>AgentSQL</code> interface for documentation.
	 * <p>
	 * It just return <code>false</code> in this implementation.
	 */
	public boolean renameCatalog( Connection conn, String OldName, String sNewName )
	{
	    // TODO: Existe un sp_renamedb nombre, nombre nuevo. Pero requiere realizar antes
	    /*       una serie de pasos a realizar para los que se debe ser propietario y sa_role:
	     * use master
	     * go
	     * sp_dboption nombre-anterior, single, true
	     * go
	     * use nombre-anterior 
	     * go
	     * checkpoint 
	     * go
	     * sp_renamedb nombre-anterior, nombre-nuevo 
	     * go
	     * use master 
	     * go
	     * sp_dboption nombre-nuevo, single, false 
	     * go
	     * use nombre-nuevo 
	     * go
	     * checkpoint
	     * go
	     */
		return false;
	}

	/**
	 * It just return <code>false</code> in this implementation.
	 * <p>
	 * See <code>AgentSQL</code> interface for documentation.
	 */
	public boolean allowsCatalogCreation()
	{
		return true;
	}

	/**
	 * It just return <code>false</code> in this implementation.
	 * <p>
	 * See <code>AgentSQL</code> interface for documentation
	 */
	public boolean allowsCatalogRename()
	{
		return false;
	}

	//-------------------------------------------------------------------------//
	// TABLE RELATED METHODS
	//-------------------------------------------------------------------------//

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 */
	public boolean createTable( Connection conn, TableDefinition table )
	{
		boolean      bSuccess = false;
		StringBuffer sb       = new StringBuffer( 1024 );

		// Create table, primary keys and foreign keys ---------------
		TableColumnDefinition[] aColumn = table.getColumns();

		sb.append( "CREATE TABLE " ).append( table.getName() ).append( " (" );

		for( int n = 0; n < aColumn.length; n++ )
			sb.append( createColumn( aColumn[n] ) ).append( ", " );

		sb.setLength( sb.length() - 2 ); // Deletes last ", "

		sb.append( createPrimaryKeys( aColumn ) );
		sb.append( createForeignKeys( table.getRelations() ) );
		sb.append( ')' );

		bSuccess = SqlUtil.execute( conn, sb.toString() );

		// Create indices -------------------------------------------
		if( bSuccess )
		{
			IndexDefinition[] aIndex = table.getIndices();

			for( int n = 0; n < aIndex.length; n++ )
				bSuccess = bSuccess && SqlUtil.execute( conn, createIndex( aIndex[n] ) );
		}

		return bSuccess;
	}

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 */
	public boolean alterTable( Connection conn, TableDefinition defOld, TableDefinition defNew )
	{
	    // TODO: se crea una copia de la antigua (tmp_old), se graban los datos, se borra la
	    //       antigua, se crea la nueva, se copian los datos de la antigua a la nueva.
		App.showMessage( "Alter table not yet implemented: it will available shortly" );
		return false;
	}

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 */
	public boolean renameTable( Connection conn, TableDefinition table, String sNewName )
	{
	    // TODO: Ejecutar sp_rename
		TableDefinition tableNew = (TableDefinition) table.clone();
		tableNew.setName( sNewName );

		return alterTable( conn, table, tableNew );
	}

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 */
	public boolean dropTable( Connection conn, DbEntityDescriptor ded )
	{
		return SqlUtil.execute( conn, "DROP TABLE " + ded.getName() );
	}

	//-------------------------------------------------------------------------//
	// COLUMNS RELATED METHODS TO SUPPORT CREATE AND ALTER TABLE COMMANDS
	//-------------------------------------------------------------------------//

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 * <p>
	 * In this default implementation only if the column name is valid is checked.
	 */
	public boolean isValidColumn( TableColumnDefinition column )
	{
		String sName = normalizeDbEntityName( column.getName(), DbEntityTypes.ENTITY_FIELD );

		return sName.length() > 0;
	}

	/**
	 * Creates the portion of the SQL command that corresponds to the passed column definition.
	 * 
	 * @param column
	 *            A table column definition
	 * @return The partial SQL command that correspondns to passed column
	 */
	protected String createColumn( TableColumnDefinition column )
	{
		StringBuffer sb = new StringBuffer( 128 );
					 sb.append( column.getName() ).append( ' ' );
					 sb.append( column.getType().getName() );

		if( isVariableLengthType( column.getType() ) )
			sb.append( '(' ).append( column.getSize() ).append( ')' );

		if( (!column.isNullable()) || (column.isPrimaryKey()) ) // SQL standard forces NOT NULL clause for primary keys columns
			sb.append( " NOT NULL" );

		if( column.getDefaultValue() != null )
		{
			String sPrefix = column.getType().getLiteralPrefix();
			String sSufix  = column.getType().getLiteralSufix();
			int    nType   = column.getType().getType();

			// If the driver does not provides this inf. this is the best approach than can be done
			if( sPrefix == null || sSufix == null && (nType == Types.CHAR || nType == Types.DATE || nType == Types.LONGVARCHAR || nType == Types.TIMESTAMP || nType == Types.VARCHAR) )
				sPrefix = sSufix = "'";

			if( sPrefix != null && sSufix != null )
				sb.append( " DEFAULT " ).append( sPrefix ).append( column.getDefaultValue() ).append( sSufix );
		}

		return sb.toString();
	}

	//-------------------------------------------------------------------------//
	// RELATIONS RELATED METHODS TO SUPPORT CREATE AND ALTER TABLE COMMANDS
	//-------------------------------------------------------------------------//

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 * <p>
	 * This default implementation checkes just only that the relation has a valid name.
	 */
	public boolean isValidRelation( RelationDefinition relation )
	{
		String sName = normalizeDbEntityName( relation.getName(), DbEntityTypes.ENTITY_RELATION );

		return (sName.length() > 0);
	}

	//-------------------------------------------------------------------------//
	// INDICES RELATED METHODS TO SUPPORT CREATE AND ALTER TABLE COMMANDS
	//-------------------------------------------------------------------------//

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 * <p>
	 * This default implementation checks that:
	 * <ul>
	 * <li>Column name is valid.
	 * <li>Index has no more columns than the maximum allowed: <code>DatabaseMetaData.getMaxColumnsInIndex()</code>.
	 * </ul>
	 */
	public boolean isValidIndex( IndexDefinition index )
	{
		String sName    = normalizeDbEntityName( index.getName(), DbEntityTypes.ENTITY_INDEX );
		int    nMaxCols = DbUtil.getAgentDBMS().getAgentMetaData().getMaxColumnsInIndex();

		return sName.length() > 0 && ((nMaxCols == 0) || (index.getColumns().length <= nMaxCols));
	}

	/**
	 * Creates the portion of the SQL command that corresponds to the primary keys for passed column definition.
	 * 
	 * @param column
	 *            A table column definition
	 * @return The partial SQL command that correspondns to primary keys for passed column
	 */
	protected String createPrimaryKeys( TableColumnDefinition[] aColumn )
	{
		StringBuffer sb = new StringBuffer( 128 );

		for( int n = 0; n < aColumn.length; n++ )
		{
			if( aColumn[n].isPrimaryKey() )
				sb.append( " PRIMARY KEY (" ).append( aColumn[n].getName() ).append( ")," );
		}

		if( sb.length() > 0 )
			sb.setLength( sb.length() - 1 ); // Deletes last ","

		return sb.toString();
	}

	/**
	 * Creates the portion of the SQL command that corresponds to the foreign keys for passed column definition.
	 * 
	 * @param column
	 *            A table column definition
	 * @return The partial SQL command that correspondns to foreing keys for passed column
	 */
	protected String createForeignKeys( RelationDefinition[] aRelation )
	{
		StringBuffer sb = new StringBuffer( 128 );

		for( int n = 0; n < aRelation.length; n++ )
		{
			// TODO : crear las claves externas
		}

		if( sb.length() > 0 )
			sb.setLength( sb.length() - 1 ); // Deletes last ","

		return sb.toString();
	}

	/**
	 * Creates the SQL command that represents the index passed as parameter.
	 * 
	 * @param index
	 *            An index definition
	 * @return The SQL command that represents the index passed as parameter
	 */
	protected String createIndex( IndexDefinition index )
	{
		StringBuffer sbIndex = new StringBuffer( 128 );
		StringBuffer sbCols  = new StringBuffer( 64 );

		// Find columns
		IndexColumnDefinition[] aCol = index.getColumns();

		for( int n = 0; n < aCol.length; n++ )
		{
			sbCols.append( aCol[n].getName() );

			if( !aCol[n].isAscending() )
				sbCols.append( " DESC" );

			sbCols.append( ", " );
		}

		sbCols.setLength( sbCols.length() - 2 ); // delete last ", "

		// Create index
		sbIndex.append( "CREATE " );

		if( index.isUnique() )
			sbIndex.append( "UNIQUE " );

		sbIndex.append( "INDEX " ).append( index.getName() ).append( " ON " ).append( index.getTable() );
		sbIndex.append( " (" ).append( sbCols ).append( ')' );

		return sbIndex.toString();
	}

	//-------------------------------------------------------------------------//
	// OTHER METHODS
	//-------------------------------------------------------------------------//

	/**
	 * See <code>AgentSQL</code> interface for documentation.
	 */
	public String getCreateDiagramTableSQLCommand()
	{
		return "CREATE TABLE " + App.sDIAGRAM_TABLE + " (SCHE VARCHAR(64), NAME VARCHAR(64), DATA TEXT)";
	}

	/**
	 * See <code>AgentSQL</code> interface for documentation.
	 * <p>
	 * In this implementation, only following types return <code>true</code>:
	 * <ul>
	 * <li>sql.Types.CHAR</li>
	 * <li>sql.Types.LONGVARCHAR</li>
	 * <li>sql.Types.VARCHAR</li>
	 * </ul>
	 */
	public boolean isVariableLengthType( TableColumnTypeDefinition tctd )
	{
		int nJdbcType = tctd.getType();

		switch( nJdbcType )
		{
			//case Types.ARRAY:
			//case Types.BIGINT:
			case Types.BINARY:
			case Types.BIT:
			case Types.BLOB:
			case Types.BOOLEAN:
			case Types.CHAR:
			//case Types.CLOB:
			//case Types.DATALINK:
			case Types.DATE:
			case Types.DECIMAL:
			//case Types.DISTINCT:
			case Types.DOUBLE:
			case Types.FLOAT:
			case Types.INTEGER:
			case Types.JAVA_OBJECT:
			//case Types.LONGVARBINARY:
			case Types.LONGVARCHAR:
			case Types.NULL:
			case Types.NUMERIC:
			//case Types.OTHER:
			case Types.REAL:
			//case Types.REF:
			case Types.SMALLINT:
			//case Types.STRUCT:
			case Types.TIME:
			case Types.TIMESTAMP:
			case Types.TINYINT:
			case Types.VARBINARY:
			case Types.VARCHAR:
				return true;
		}

		return false;
	}

	/**
	 * See <code>AgentSQL</code> interface for documentation
	 */
	public String normalizeDbEntityName( String sName, int nEntityType )
	{
		AgentMetaData amd      = DbUtil.getAgentDBMS().getAgentMetaData();
		char[]        acName   = sName.trim().toCharArray();
		StringBuffer  sbNormal = new StringBuffer( acName.length );
		String        sExtra   = amd.getExtraNameCharacters();
		int           nMaxLen  = 30;
		String        sEntity  = "Unknown";
		String        sNormalized;

		switch( nEntityType )
		{
			case DbEntityTypes.ENTITY_CATALOG:
				nMaxLen = amd.getMaxCatalogNameLength();
				sEntity = "Catalog";
				break;
			case DbEntityTypes.ENTITY_DIAGRAM:
				nMaxLen = amd.getMaxDiagramNameLength();
				sEntity = "Diagram";
				break;
			case DbEntityTypes.ENTITY_PROCEDURE:
				nMaxLen = amd.getMaxProcedureNameLength();
				sEntity = "Procedure";
				break;
			case DbEntityTypes.ENTITY_SCHEMA:
				nMaxLen = amd.getMaxSchemaNameLength();
				sEntity = "Schema";
				break;
			case DbEntityTypes.ENTITY_TABLE:
				nMaxLen = amd.getMaxTableNameLength();
				sEntity = "Table";
				break;
			case DbEntityTypes.ENTITY_RELATION:
				nMaxLen = amd.getMaxTableNameLength();
				sEntity = "Table";
				break; // It's reasonable that tables & relations will have same length
			case DbEntityTypes.ENTITY_INDEX:
				nMaxLen = amd.getMaxTableNameLength();
				sEntity = "Table";
				break; // It's reasonable that tables & indices will have same length
			case DbEntityTypes.ENTITY_UDT:
				nMaxLen = amd.getMaxProcedureNameLength();
				sEntity = "UDT";
				break; // It's reasonable that procedures & UDTs will have same length
			case DbEntityTypes.ENTITY_FIELD:
				nMaxLen = amd.getMaxColumnNameLength();
				sEntity = "Field";
				break;
		}

		nMaxLen = Math.min( acName.length, nMaxLen );

		for( int n = 0; n < nMaxLen; n++ )
		{
			if( Character.isLetterOrDigit( acName[n] ) || acName[n] == '_' || sExtra.indexOf( acName[n] ) > -1 )
				sbNormal.append( acName[n] );
		}

		sNormalized = sbNormal.toString();

		if( !amd.storesMixedCaseIdentifiers() )
		{
			if( amd.storesUpperCaseIdentifiers() )
				sNormalized = sNormalized.toUpperCase();
			else
				sNormalized = sNormalized.toLowerCase();
		}

		if( sbNormal.length() < sName.length() )
		{
			String sCase = amd.storesMixedCaseIdentifiers() ? "Mixed case" : amd.storesUpperCaseIdentifiers() ? "Upper case only" : "Lower case only";

			App.showMessage( "Name was normalized", "Original   name = '" + sName + "'\n" + "Normalized name = '" + sbNormal + "'\n\n" + "Restrictions for " + sEntity
					+ " names:\n" + "   > Letters from 'A' to 'Z' and from 'a' to 'z' plus '_'\n" + "   > " + sCase + " allowed\n" + "   > Special characters: \"" + sExtra
					+ "\"\n" + "   > Max identifier length: " + nMaxLen );
		}

		if( sbNormal.length() == 0 )
			App.showMessage( "Invalid name: all characters are invalid." );

		return sNormalized;
	}
}